Advanced Reporting FAQ

Fields displayed in report columns can be connected to a WorkCenter window. When a field is connected to a WorkCenter window, clicking the field opens the designated WorkCenter in a new window.

Multiple smart actions can be specified for different fields/columns in your report. If the Smart Action Type setting is set to Column, all smart actions defined for columns in the Report Field Options window are honored.

Connect a report field to a WorkCenter window:

1. Click the Smart Elements tab.

2. Ensure that the value of the Smart Mode field is set to On.

3. Click the Smart Actions sub-tab.

4. Select Column (Multiple Smart Action per Row) from the Smart Action Type field.

   No additional fields display on the Smart Actions sub-tab when Column is selected for the Smart Action Type.

5. Select the field you want to use for a smart action from the Display field in the upper-right corner of the Report Setup window.

   For example, to make the Target Date field in a work order report editable, you would select the Target Date field from the list.

6. Click the Edit button directly below the Display field.

   The Report Field Options window opens.

7. Select Popup Work Center from the Smart Action field.

   A Smart Action Field Value field appears, prompting you to select the field value that determines the record to be opened. In most cases, you will be selecting the primary field from the record, such as [WO] Work Order or [Asset] Asset.

8. Enter the tool tip you want to appear when the user hovers their cursor over the field in the Smart Action Tool Tip field.

   For example, you could configure the tool tip to say: Click here to open this work order.

9. Click the Apply button.

   The window closes.

10. Repeat as necessary.

11. Click the Apply button.

    The Report Preview window opens. If you accessed the Report Setup window from the Report List, you are returned to the Report List.

Fields displayed in report columns can be connected to a Report window. When a field is connected to a Report window, clicking the field opens the selected report in a new window.

Multiple smart actions can be specified for different fields/columns in your report. If the Smart Action Type setting is set to Column, all smart actions defined for columns in the Report Field Options window are honored.

Connect a report field to a Report window:

1. Click the Smart Elements tab.

2. Ensure that the value of the Smart Mode field is set to On.

3. Click the Smart Actions sub-tab.

4. Select Column (Multiple Smart Action per Row) from the Smart Action Type field.

   No additional fields display on the Smart Actions sub-tab when Column is selected for the Smart Action Type.

5. Select the field you want to use for a smart action from the Display field in the upper-right corner of the Report Setup window.

   For example, to make the Target Date field in a work order report editable, you would select the Target Date field from the list.

6. Click the Edit button directly below the Display field.

   The Report Field Options window opens.

7. Select Popup Report from the Smart Action field.

   Several fields appear.

8. Select the report that should open when the field is clicked from the Smart Action Report field.

   Reports are listed in alphabetical order.

9. Select the criteria that should be used to determine the records that should appear in the report from the Smart Action Report Criteria field.

   In most cases, you select the primary field for the record, such as [WO] Work Order or [Asset] Asset.

10. Enter the tool tip you want to appear when the user hovers their cursor over the field in the Smart Action Tool Tip field.

    For example, you could configure the tool tip to say: Click here to open the Work Order Status Report.

11. Click the Apply button.

    The window closes.

12. Repeat as necessary.

13. Click the Apply button.

    The Report Preview window opens. If you accessed the Report Setup window from the Report List, you are returned to the Report List.

Report rows can be connected to WorkCenter or Report windows. When a report row is connected to a WorkCenter window, clicking the row opens the selected WorkCenter in a new window.

Only a single action is enabled when Row is selected in the Smart Action Type field. You cannot specify both a smart action for a row and smart actions for columns. If the Smart Action Type field is set to Row, any smart actions defined for columns in the Report Field Options window are ignored.

Connect a report row to a WorkCenter window:

1. Click the Smart Elements tab.

2. Ensure that the value of the Smart Mode field is set to On.

3. Click the Smart Actions sub-tab.

4. Select Row (Single Smart Action per Row) from the Smart Action Type field.

   Once Row has been selected, additional fields appear.

5. Select Popup WorkCenter from the Smart Action Dropdown field.

   The Smart Action Field Value and Smart Action Tool Tip fields appear.

6. Select the field value that will determine which record to open from the Smart Action Field Value field.

   In most cases, you select the primary field for the record, such as [WO] WO or [Asset] Asset.

7. Enter the tool tip you want to appear when the user hovers their cursor over the row in the Smart Action Tool Tip field.

   For example, you could configure the tool tip to say: Click to open this work order.

8. Click the Apply button.

   The Report Preview window opens. If you accessed the Report Setup window from the Report List, you are returned to the Report List.

Report rows can be connected to Report or WorkCenter windows. When a report row is connected to a Report window, clicking the row opens the selected report in a new window. This option might be used to access additional detail about the displayed record. For example, a report that lists recently closed work orders might be connected to a report that provides additional cost information regarding the selected work order.

Only a single action is enabled when Row is selected in the Smart Action Type field. You cannot specify both a smart action for a row and smart actions for columns. If the Smart Action Type field is set to Row, any smart actions defined for columns in the Report Field Options window are ignored.

Connect a report row to a Report window:

1. Click the Smart Elements tab.

2. Ensure that the value of the Smart Mode field is set to On.

3. Click the Smart Actions sub-tab.

4. Select Row (Single Smart Action per Row) from the Smart Action Type field.

   Once Row has been selected, additional fields appear.

5. Select Popup Report from the Smart Action field.

6. Select the report that should be opened when the row is clicked from the Smart Action Report field.

   Reports are listed in alphabetical order.

7. Select the criteria that should be used to determine the records that should appear in the report from the Smart Action Report Criteria field.

   In most cases, you select the primary field for the record, such as [WO] WO or [Asset] Asset.

8. Enter the tool tip you want to appear when the user hovers their cursor over the row in the Smart Action Tool Tip field.

   For example, you could configure the tool tip to say: Click to open the Work Order Report.

9. Click the Apply button.

   The Report Preview window opens. If you accessed the Report Setup window from the Report List, you are returned to the Report List.

Action buttons are defined on the Smart buttons sub-tab of the Smart Elements tab.

The Smart Buttons field lists numerous options that can be selected. Since only one option can be selected from this field, options for multiple buttons are displayed with a slash separating the options.

If editable fields have been defined for this report using the Report Field Options window, a Save button must be selected. However, if there will be other smart buttons defined on the report, it is not necessary to select the Save button. Select a different button, and Maintenance Connection will automatically detect the need for the Save button and add it to the report.

Some options are similar, but one is labeled as (No Dialog). These options correspond to transactions that are associated with the Complete/Close window.

If you want to have the user view and update the Complete/Close window after clicking the button on the report, be sure to select the option that does not have the (No Dialog) label.

For instructions on adding action buttons to a report, see: Smart Buttons sub-tab.

The following action buttons are available:

• Save

• Delete

• Issue

• Issue/Deny

• Issue/Deny/Response/Complete

• On-Hold

• On-Hold / Issue

• Respond

• Respond (No Dialog)

• Complete

• Complete (No Dialog)

• Finalize

• Finalize (No Dialog)

• Close

• Close (No Dialog)

• Complete/Close

• Complete/Close (No Dialog)

• Issue/Complete

• Issue/Complete (No Dialog)

• Respond, Complete, Finalize, Close

• Respond, Complete, Finalize, Close (No Dialog)

• Respond, Complete

• Respond, Complete (No Dialog)

• Respond, Close

• Respond, Close (No Dialog)

• Cancel

• Deny

• Reissue

• Approve/reject

Editable fields can be displayed on a report, allowing appropriate members to update data directly in the report. Editable fields in a report appear with a Save button to allow each row to be saved. A Save button is also available at the top of the report and on group headers to save changes on multiple rows in one step. As records are saved, the text in the row changes to be green and italicized to indicate that changes have been made.

Providing editable fields in a report is a very convenient feature, as multiple records can be updated on a single page. The report can also be set up to include only fields of importance for the audience that will be updating this form. As such, this feature provides a mechanism to create custom editable forms that allow members to quickly update important data, without the need to navigate to different parts of the system. Editable reports or forms can even be emailed to specified individuals, both members and non-members.

Editable reports are a very powerful feature, thus it is important that reports are set up correctly. We recommend that only data from the primary file, such as work order data in a work order report, is enabled for updating.

Make a report field editable:

1. Click the Smart Elements tab.

2. Ensure that the value of the Smart Mode field is set to On.

3. Click the Smart Actions sub-tab.

4. Select Column (Multiple Smart Action per Row) from the Smart Action Type field.

   No additional fields display on the Smart Actions sub-tab when Column is selected for the Smart Action Type.

5. Click the Smart buttons sub-tab.

6. Select Save from the Smart Button(s) field.

   If you want to have another smart button displayed on the report, such as Issue, you do not have to select the Save button. Maintenance Connection detects that two options have been specified and automatically adds the Save button for you. However, if no other smart button action is specified, you must select Save from this field.

7. Select the field value of the record that is being edited from the Smart Button Field Value field.

   Since editable reports are a very powerful feature, we recommend you only update primary file data. As such, a value such as [WO]Work Order or [Asset] Asset should be selected.

8. Select the field you want users to edit from the Display field in the upper-right corner of the Report Setup window.

9. Click the Edit button.

   The Report Field Options window opens.

10. Select Edit Field from the Smart Action field.

11. Enter the tool tip you want to appear when the user hovers their cursor over the field in the Smart Action Tool Tip field.

    For example, you could configure the tool tip to say: Click Save if value is edited.

12. Click the Apply button.

13. Repeat steps 8-12 to configure another field on the report for editing.

14. Click the Apply button.

    The Report Preview window opens. If you accessed the Report Setup window from the Report List, you are returned to the Report List.

    For information on viewing and using editable reports, see: Editable Fields in Preview window.

The Maintenance Connection Report Smart Email feature allows you to email editable forms and reports to users of the system, as well as third-party individuals without system login credentials. The Smart Email sub-tab, which appears on both the Smart Elements tab and the Schedule tab, allows you to establish email distribution settings for the current report, including security settings or protocols. These settings are in effect when reports are emailed manually as well as when they are automatically generated on a scheduled basis.

The Smart Email feature provides an expedient method for email recipients to update information without the need to navigate or view other parts of the system. Recipients of the email only have access to the report form and defined smart elements; they will not have general access to system features or report setup.

As such, this feature can be used for third-party recipients (such as a customer or contractor), allowing them to update pertinent information. Numerous settings are available to ensure appropriate security. Users can be forced to log into the system to access the smart report, or automatic login can be enabled.

The Auto-Login functionality does not work in conjunction with the Rules Manager option to send reports as an attachment. Configuring a rule to send a report with Auto-Login enabled results in the report not displaying as an attachment in the sent email.

For further information, see:

• Establish Email Settings for Smart Reports

• Send Smart Email Reports from Preview window

• View Smart Elements in Report Review window

Summary Reports perform aggregate calculations on cumulative (grouped) data. Establishing or modifying summary reports is an advanced feature that should only be used by members who are comfortable with database reporting tools.

To create your own summary report, you must first find a template report from the same report group. This means you must find an existing summary report that uses the same type of data and copy that report to create your own report.

The following general instructions are offered as a guide for those who have the requisite skills and experience to create a summary report:

1. Locate a Summary report you want to use as the template for the new summary report.

   You can confirm it is a summary by looking at the General >Settings sub-tab.

2. Copy the report.

3. Access Report Setup for the new report.

4. Determine the field on which you want to aggregate the summary calculations.

   This is typically a text field, such as department, shop, or priority. Ensure the field is listed as a field in the Display field at the top of the Report Setup window (it is typically the first field).

5. Ensure the aggregation field selected in step 5 is listed as a sort field on the General > Sort/Group sub-tab.

   Multiple levels of aggregation can be performed. For example, you could list Shop and then Priority. Both of these fields would need to be listed in the Display field and the General > Sort/Group sub-tab.

   You can create a field to count records. You can use any field as a placeholder to perform the count, but remember that Maintenance Connection only counts fields that have values. So be sure to select a required field so that all records will be counted.

6. Create a count field:

   1. Select a required field from the Available field.

      For example, for a work order report, you could select Work Order ID / # or Work Order Status, as these fields always have a value.

   2. Click the Move Arrow button or double-click the field name.

      The field is moved to the bottom of the Display field on the right.

   3. Select the placeholder field in the Display field and click the Edit button.

      The Report Field Options window opens for this field.

   4. Enter the label you want for the column in the Field Label (Custom) field.

      An example field label could be Total Count.

   5. Enter COUNT in the Aggregate Function field.

   6. Click the Apply button.

      You are returned to the Report Setup window.

7. Perform a summary / aggregate calculation on an existing numeric field, such as Total Costs:

   1. Select the field you want to perform the summary calculation on from the Available field.

   2. Click the Move Arrow button or double-click the field name.

      The field is moved to the bottom of the Display field on the right.

   3. Select the placeholder field in the Display field and click the Edit button.

      The Report Field Options window opens for this field.

   4. Enter the label you want for the column in the Field Label (Custom) field.

      An example field label could be Total Cost.

   5. Enter the type of aggregation to be performed in the Aggregate Function field.

   6. Click the Apply button.

      You are returned to the Report Setup window.

      After running this report, the field name in the Display field updates to reflect the aggregate calculation, such as: SUM(Total Cost).

8. Repeat step 8 for any additional calculations to be performed on a numeric field.

   Different functions can be performed on the same field. To perform an average calculation, use AVG for the Aggregate Function.

9. Click the Apply button.

   The Report Preview window opens. If you accessed the Report Setup window from the Report List, you are returned to the Report List.

Custom expressions can be used to modify the format of a field or perform a calculation. For example, you can use a custom expression to change the display of a date field (remove time stamp) or change the case (display all in upper case). You can also perform mathematical calculations, which is particularly useful for determining elapsed time. You can perform mathematical calculations on individual fields, or perform calculations between fields, such as the difference between dates.

You can use all standard mathematical operations to create calculations (+ for addition, - for subtraction, * for multiplication and / for division).

To create a custom expression, you must determine the field names for the fields on which the calculation will be performed. This is the field name specified in table.fieldname format. There are two methods to determine this formal Field Name:

1. If the field is already specified on the report, select the field in the Display field and click the Edit button. The actual field name will be listed in table.fieldname format at the top of the Report Field Options window.

2. Locate the field on the appropriate page and click the field label. The Help window opens, listing the Field Name at the top of the window.

As a simple example, we will create a custom expression that can be used to create a report that calculates the costs of re-ordering inventory items, presuming a 10% increase in cost:

1. Determine the proper field names (table.fieldname format) for the fields you will use in your calculation.

2. Access Report Setup for the report where you will put the calculated field.

3. Select a field from the Available field to use as a placeholder to contain your calculation.

   You can use any field as a placeholder to perform the calculation, but we recommend you use a field of the same data type.

   As an example, you could select [Part] Average Price and move it to the Display field.

4. Select the placeholder field in the Display field.

5. Click the Edit button.

   The Report Field Options window opens for the field.

6. Enter the label for your calculated field in the Field Label (Custom) field.

7. Select the appropriate field type for your calculated result from the Field Type field.

   If the placeholder field shares the same type as your desired output, you do not need to select a type from the Field Type (Custom) field.

8. Select the Yes option button for the Custom Expression? setting.

9. Enter your expression in the Custom Expression field.

   For example, to create a custom expression that calculates a 10% increase in last purchase cost, this calculation is performed by multiplying the last purchase cost by 1.10% (this calculates a 10% increase and then adds it to the original cost). The custom expression would be:

   Part.LastOrderUnitPrice*1.1

10. Click the Apply button.

    The window closes.

11. Click the Apply button.

    The Report Preview window opens. If you accessed the Report Setup window from the Report List, you are returned to the Report List.

Common examples of calculated fields using custom expressions include the following:

• Add two field values together

  • Example Requirements: Work Order Labor Cost + Work Order Parts Cost

    Custom Expression: WO.CostLaborActual+WO.CostPartActual

• Perform a mathematical calculation on a field

  • Example Requirements: Calculate a 10% increase in labor cost

    Custom Expression: WO.CostLaborActual *1.1

• Round a numeric field

  This is especially valuable for summary calculations, such as averages, where a large number of decimals may be returned. In addition, this is often used to round meter readings:

  • Example Requirements: Round to whole number

    Custom Expression: ROUND(WO.TargetHours,0)

  • Example Requirements: Round to nearest tenth

    Custom Expression: ROUND(Asset.Meter1Reading,1)

  • Example Requirements: Round to nearest hundredth

    Custom Expression: ROUND(Asset.Meter1Reading,2)

• Remove the time display from a date field

  • Example Requirements: Return the left portion of the Work Order Requested field, eliminating the display of the time

    Custom Expression: LEFT(WO.Requested,12)

• Perform a date function on a field

  The proper format for this kind of expression is DATEDIFF(increment to display - such as dd for days, field / value to be subtracted, value to subtract from)

  • Example Requirements: Display the number of days a work order was open before completion

    Custom Expression: DATEDIFF(dd,WO.Requested,WOComplete)

  • Example Requirements: Display the number of days that have elapsed since an open work order was requested

    Custom Expression: DATEDIFF(dd,WO.Requested,,getdate())

    The getdate() function is used to indicate the current date should be used in a formula. Also, be sure to specify the correct Field Type, which would be number or integer if the result will be returned in days.

The Maintenance Connection Reporter provides options to create multi-series charts, such as multi-bar, stacked, and charts with a trend line. These chart styles allow for a more complex graphical presentation of your data, displaying data along multiple dimensions. Multi-series charts are more complex to create and thus require more familiarity with this type of charting.

1. Open the Report Setup window for the report you want to add the chart to.

2. Click the Chart / KPI tab.

3. Select Bar Chart (Multi) from the Chart Type field.

   The fields on the page change to support the definition of this type of chart.

4. Select the field by which the charted data will be grouped into sections from the Chart By field.

5. Enter the chart name in the Chart Name field.

6. Select the Show Values check box if you want to have the actual values of each bar displayed in the chart.

   The calculations for the individual bars are defined in the Chart Function data area.

7. To define your first bar type:

   1. Select the data value you want to display for the bar from the Data Value field.

   2. Enter the label for the bar type in the Label field.

   3. Select the function of the bar from the Function field.

   4. Select the color of the bar from the Color field.

   5. Click the Add button.

      The data for the bar appears in a list.

8. Repeat step 7 for each bar.

   For the first calculation, we selected [WO] Target Hours for the Data Value, Target Hours for the Label, Sum for the Function, and light blue for the color.

   For the second calculation, we selected [WO] Actual Hours for the Data Value, Actual Hours for the Label, Sum for the Function, and green for the color.

   The third calculation was simply a count. As such, any field could be used as all fields return the same count of records. We selected a field, entered WO Count for the Label, Count for the Function, and selected purple for the color.

9. To change the display order of the bars, use the up and down arrows.

10. To remove a field, click the Red X.

11. Click the Apply button to preview your chart.

Organizations can report on Work Order statistics across multiple business units, giving Service Level Providers (including Cloud-hosted organizations that service multiple MC databases) access to a group of reports that deliver SLA Work Order metrics.

Customers running a single database and those who utilize multiple separate databases benefit from this feature by having Work Order reports configured to pull data across all their Repair Centers and Departments.

Cloud Hosted customers pre-configured with multiple databases in a Master-Child setup get the additional benefit of being able to roll up pre-aggregated metrics from the child databases on a monthly basis.

As with normal Reporter functions, these standard base reports can be copied and modified for your purposes, and you can use Report Criteria to view the data most pertinent to you. This includes allowing Service Level Providers to modify Report Criteria to include or preclude specific databases.

To access these cross-business unit reports:

1. Access the Reporter.

2. Select the Work Order (Open / Closed)... report group.

   The reports with a title starting with WO Metrics Report... pull Work Order data from all business units. Reports are available for Corrective and Predictive Work Orders, or both. These reports are organized either by Repair Center or Department. Standard reporting practices can be used to modify these reports and leverage them for your organization's benefit.

Using pivot tables, data typically presented in separate rows can be aggregated into dynamically generated columns. Similar to the functionality provided in spreadsheet software, pivot tables allow you to switch the view of data between columns and rows to provide useful summaries of data.

Pivot tables can be used for both detail and summary reports.

Redefining this report as a pivot table, we can pivot the shops to columns and show total costs by defined type of work order. This format can be much more beneficial when viewing large quantities of data.

Pivot table Definition:

• Row: [WO]Type Desc

• Column: [WO]Shop Name

• Summary Data: [WO]Cost Total Actual

Similarly, the following example shows a pivot report designed to show total work hours assigned to each labor source per month. The pivot logic dynamically calculates assignment hours by month of assignment.

Pivot table Definition:

• Row: [WOAssign]Labor Name

• Column: [WOAssign]Assigned Date, using Months function

• Summary Data: [WOAssign]Assigned Hours

Pivot table Definition:

• Row: [WOAssign]Labor Name

• Column: [WOAssign]Assigned Date, using Weekdays function

• Summary Data: [WOAssign]Person?

Create a pivot table report:

1. Locate a report to be used as the template for the new pivot report.

2. Copy the report.

   Be sure to select a Summary Report if the pivot report will include summary or aggregate calculations (such as total costs).

3. Access Report Setup for the new report.

4. Determine the fields you will use to define your pivot report.

   You will need three fields, each of which will be used for a specific purpose:

   • Row: Field that will be displayed as rows in the pivot table report. This is typically a text field.

   • Column: Field that will be used to aggregate columns of data. This can be a text field (such as department, priority, shop) or an aggregation of numeric or date data.

   • Summary Calculation: Field that will be calculated or aggregated by the defined column and row. The summary field needs to be numeric (unless you are performing a count aggregation). If you are performing a count, be sure to select a field that is ALWAYS populated (like WorkOrderID).

5. Ensure the fields you will use to define the row, column, and summary calculation are listed in the Display field in this exact order: row, column, summary.

6. Remove any fields that are not needed for the report.

7. Select the field you want to use for your row definition from the Display field.

8. Click the Edit button.

   The Field Options (Properties) window opens.

9. Select Row from the Pivot Setup field.

10. Click the Apply button.

    The window closes.

11. Select the field you want to use for your column definition from the Display field.

12. Click the Edit button.

    The Field Options (Properties) window opens.

13. Select Column from the Pivot Setup field.

    A new field appears, prompting you to specify how the column should be defined.

14. Select how the columns should be defined from the Columns Defined Using field.

    For most text fields, columns are defined using the default Field Data. For date aggregations, options exist to aggregate the data logically. For example, dates can be aggregated into months, quarters, or weeks.

    When a function is selected for the Columns Defined Using field, Maintenance Connection automatically places the expression used to create that function in the Custom Expression field in the bottom of the window. Change the Custom Expression setting to Yes whenever a custom expression is defined.

15. Click the Apply button.

    The window closes.

16. Select the field you want to use for your summary calculation from the Display field.

17. Click the Edit button.

    The Field Options (Properties) window opens.

18. Enter the type of aggregation being performed in the Aggregate Function field.

    For example, SUM, COUNT, AVG.

19. Select Summary Data from the Pivot Setup field.

20. Click the Apply button.

    The window closes.

21. Click the General > Sort/Group tab.

22. Ensure that your sort fields logically represent the data being presented.

    The field specified for row definition must be listed as a sort field.

23. Click the Apply button.

    The Report Preview window opens. If you accessed the Report Setup window from the Report List, you are returned to the Report List.